﻿Imports System.Data.SqlClient
Imports Control_Vuelos_BE

Public Class ProgramaViajeADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion.GetCnx)
    Dim cmd As New SqlCommand
    Dim lExito As Boolean

    Public Function pr_CargarTipoVueloCupos(ByVal sCodTipo As String) As Integer
        Dim dtr As SqlDataReader
        Dim nCupos As New Integer
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT NroCupos as cupos FROM dbo.M_TipoVuelo WHERE (CodTipo='{0}')", sCodTipo)
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                nCupos = dtr(0)
            Else
                nCupos = 0
            End If
        Catch x As SqlException
            MsgBox("ProgramaViajeADO  (pr_CargarTipoVuelo) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return nCupos
    End Function

    Public Function pr_CrearProgramaViaje(ByVal pvBE As V_ProgramaViajeBE) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_InsertarProgramaViaje"
        Try
            With pvBE
                cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
                cmd.Parameters("@psCodViaje").Value = .CodViaje
                cmd.Parameters.Add(New SqlParameter("@psCodContrataViaje", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodContrataViaje").Value = .CodContrataViaje
                cmd.Parameters.Add(New SqlParameter("@psCodEmpresa", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodEmpresa").Value = .CodEmpresa
                cmd.Parameters.Add(New SqlParameter("@psCodLugarOrigen", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarOrigen").Value = .CodLugarOrigen
                cmd.Parameters.Add(New SqlParameter("@psCodLugarDestino", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarDestino").Value = .CodLugarDestino
                cmd.Parameters.Add(New SqlParameter("@psCodTipoVuelo", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodTipoVuelo").Value = .CodTipoVuelo
                cmd.Parameters.Add(New SqlParameter("@pdFchViaje", SqlDbType.DateTime))
                cmd.Parameters("@pdFchViaje").Value = .FchViaje
                cmd.Parameters.Add(New SqlParameter("@pnNroCupos", SqlDbType.Int))
                cmd.Parameters("@pnNroCupos").Value = .NroCupos
                cmd.Parameters.Add(New SqlParameter("@pnPsoMaxViaje", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoMaxViaje").Value = .PsoMaxViaje
                cmd.Parameters.Add(New SqlParameter("@psObsViaje", SqlDbType.Text))
                cmd.Parameters("@psObsViaje").Value = .ObsViaje
                cmd.Parameters.Add(New SqlParameter("@psEstadoViaje", SqlDbType.VarChar, 1))
                cmd.Parameters("@psEstadoViaje").Value = .EstadoViaje
                cmd.Parameters.Add(New SqlParameter("@psCodCreadopor", SqlDbType.Char, 20))
                cmd.Parameters("@psCodCreadopor").Value = .CodCreadopor
                cmd.Parameters.Add(New SqlParameter("@pdFchCreado", SqlDbType.DateTime))
                cmd.Parameters("@pdFchCreado").Value = .FchCreado
            End With
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("ProgramaViajeADO (pr_CrearProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function pr_EliminarProgramaViaje(ByVal sCodpv As String) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_EliminarProgramaViaje"
        Try
            cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
            cmd.Parameters("@psCodViaje").Value = sCodpv
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("ProgramaViajeADO (pr_EliminarProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function pr_Listar_ProgramaViaje(ByVal sCodUser As String, ByVal nOrd As Integer) As DataTable
        Dim sCond As String = IIf(nOrd = 1, "(pv.EstadoViaje=1) AND", "")
        Dim dt As New DataTable
        Dim sql As String = String.Format("SELECT pv.CodContrataViaje,mc.desContrataViaje,pv.CodViaje,pv.FchViaje,pv.CodEmpresa,et.NomEmpresa,pv.NroCupos,pv.CodLugarOrigen,lo.NomLugar as DesLugarOrigen, pv.CodLugarDestino,ld.NomLugar as DesLugarDestino,pv.PsoMaxViaje,pv.ObsViaje,pv.EstadoViaje,pv.CodTipoVuelo,ev.desEstadoVuelo FROM dbo.V_ProgramaViaje pv INNER JOIN dbo.M_EmpresaTransporte et ON pv.CodEmpresa=et.CodEmpresa INNER JOIN M_Lugar lo ON pv.CodLugarOrigen=lo.CodLugar INNER JOIN M_Lugar ld ON pv.CodLugarDestino=ld.CodLugar INNER JOIN M_Contratante mc ON pv.CodContrataViaje=mc.CodContrataViaje INNER JOIN dbo.M_Estado_Vuelo ev ON pv.EstadoViaje=ev.codEstadoVuelo WHERE {0} (pv.CodLugarOrigen IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='{1}')))  ORDER BY {2}", sCond, sCodUser, IIf(nOrd = 1, "pv.CodContrataViaje,pv.FchViaje", "pv.fchviaje DESC"))
        Try
            cnx.Open()
            Using da As New SqlDataAdapter(sql, cnx)
                da.Fill(dt)
            End Using
            cnx.Close()
            Return dt
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (pr_Listar_ProgramaViaje) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarProgramaViajeTreeList(ByVal sCodUser As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeList", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodUser", SqlDbType.Char)
                    cmd.Parameters("@p_CodUser").Value = sCodUser
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (pr_ListarProgramaViajeTreeList) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarProgramaViajeTreeListCerrado(ByVal sCodUser As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeListCerrado", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodUser", SqlDbType.Char)
                    cmd.Parameters("@p_CodUser").Value = sCodUser
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (pr_ListarProgramaViajeTreeListCerrado) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarProgramaViajeTreeListChecking(ByVal p_CodUser As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeListChecking", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodUser", SqlDbType.VarChar, 20)
                    cmd.Parameters("@p_CodUser").Value = p_CodUser
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (pr_ListarProgramaViajeTreeListChecking) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarProgramaViajeTreeListCheckingCerrado(ByVal p_CodUser As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeListCheckingCerrado", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodUser", SqlDbType.VarChar, 20)
                    cmd.Parameters("@p_CodUser").Value = p_CodUser
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (pr_ListarProgramaViajeTreeListCheckingCerrado) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ModificarProgramaViaje(ByVal pvBE As V_ProgramaViajeBE) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_ModificarProgramaViaje"
        Try
            With pvBE
                cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
                cmd.Parameters("@psCodViaje").Value = .CodViaje
                cmd.Parameters.Add(New SqlParameter("@psCodContrataViaje", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodContrataViaje").Value = .CodContrataViaje
                cmd.Parameters.Add(New SqlParameter("@psCodEmpresa", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodEmpresa").Value = .CodEmpresa
                cmd.Parameters.Add(New SqlParameter("@psCodLugarOrigen", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarOrigen").Value = .CodLugarOrigen
                cmd.Parameters.Add(New SqlParameter("@psCodLugarDestino", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarDestino").Value = .CodLugarDestino
                cmd.Parameters.Add(New SqlParameter("@psCodTipoVuelo", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodTipoVuelo").Value = .CodTipoVuelo
                cmd.Parameters.Add(New SqlParameter("@pdFchViaje", SqlDbType.DateTime))
                cmd.Parameters("@pdFchViaje").Value = .FchViaje
                cmd.Parameters.Add(New SqlParameter("@pnNroCupos", SqlDbType.Int))
                cmd.Parameters("@pnNroCupos").Value = .NroCupos
                cmd.Parameters.Add(New SqlParameter("@pnPsoMaxViaje", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoMaxViaje").Value = .PsoMaxViaje
                cmd.Parameters.Add(New SqlParameter("@psObsViaje", SqlDbType.Text))
                cmd.Parameters("@psObsViaje").Value = .ObsViaje
                cmd.Parameters.Add(New SqlParameter("@psEstadoViaje", SqlDbType.VarChar, 1))
                cmd.Parameters("@psEstadoViaje").Value = .EstadoViaje
                cmd.Parameters.Add(New SqlParameter("@psCodModificadopor", SqlDbType.Char, 20))
                cmd.Parameters("@psCodModificadopor").Value = .CodModificadopor
                cmd.Parameters.Add(New SqlParameter("@pdFchModificado", SqlDbType.DateTime))
                cmd.Parameters("@pdFchModificado").Value = .FchModificado
            End With
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("ProgramaViajeADO (pr_ModificarProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function pr_NuevoCorrelativo(ByVal sNomLugar As String) As String
        Dim sDia, sMes, sAno, sBus, sNewCod, sCodAct As String
        sAno = Microsoft.VisualBasic.Right(Date.Today, 4)
        sMes = Microsoft.VisualBasic.Mid(Date.Today, 4, 2)
        sDia = Microsoft.VisualBasic.Left(Date.Today, 2)
        sBus = String.Format("{0}{1}{2}" & sNomLugar, sAno, sMes, sDia)
        Try
            cnx.Open()
            With cmd
                .Connection = cnx
                .CommandType = CommandType.Text
                .CommandText = String.Format("SELECT TOP 1 RIGHT(CodViaje,3) FROM dbo.V_ProgramaViaje WHERE LEFT(CodViaje ,9)='{0}' ORDER BY CodViaje DESC", sBus)
                sCodAct = .ExecuteScalar()
            End With
            If sCodAct <> "" Then
                sNewCod = sBus & Right(CStr(Val(sCodAct) + 10001), 3)
            Else
                sNewCod = sBus & "001"
            End If
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (pr_NuevoCorrelativo) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        Finally
            cnx.Close()
        End Try
        Return sNewCod
    End Function

    Public Function pr_TraerProgramaViaje(ByVal sCodpv As String) As V_ProgramaViajeBE
        Dim dtr As SqlDataReader
        Dim _pvBE As New V_ProgramaViajeBE
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "pr_TraerProgramaViaje"
            cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
            cmd.Parameters("@psCodViaje").Value = sCodpv
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                With _pvBE
                    .CodViaje = dtr("CodViaje")
                    .CodContrataViaje = dtr("CodContrataViaje")
                    .CodEmpresa = dtr("CodEmpresa")
                    .CodLugarOrigen = dtr("CodLugarOrigen")
                    .CodLugarDestino = dtr("CodLugarDestino")
                    .CodTipoVuelo = dtr("CodTipoVuelo")
                    If IsDBNull(dtr("FchViaje")) Then
                        .FchViaje = DateTime.Now
                    Else
                        .FchViaje = dtr("FchViaje")
                    End If
                    .NroCupos = dtr("NroCupos")
                    .PsoMaxViaje = dtr("PsoMaxViaje")
                    .ObsViaje = dtr("ObsViaje")
                    .EstadoViaje = dtr("EstadoViaje")
                    If IsDBNull(dtr("CodCreadopor")) Then
                        .CodCreadopor = ""
                    Else
                        .CodCreadopor = dtr("CodCreadopor")
                    End If
                    If IsDBNull(dtr("FchCreado")) Then
                        .FchCreado = DateTime.Now
                    Else
                        .FchCreado = dtr("FchCreado")
                    End If
                    If IsDBNull(dtr("CodModificadopor")) Then
                        .CodModificadopor = ""
                    Else
                        .CodModificadopor = dtr("CodModificadopor")
                    End If
                    If IsDBNull(dtr("FchModificado")) Then
                        .FchModificado = DateTime.Now
                    Else
                        .FchModificado = dtr("FchModificado")
                    End If
                End With
            End If
        Catch x As SqlException
            MsgBox("ProgramaViajeADO (pr_TraerProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return _pvBE
    End Function
End Class
